﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Quail.Helpers;

namespace Quail
{
    /// <summary>
    /// Provides simple SQL data adapter capabilities
    /// </summary>
    public abstract class SimpleDataAdapter<TShell> : TestShellSubClass<TShell>
        where TShell : ITestShell
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="SimpleDataAdapter&lt;TShell&gt;"/> class.
        /// </summary>
        /// <param name="shell">The test shell.</param>
        protected SimpleDataAdapter(TShell shell)
            :base(shell)
        {
            LogQuery = true;
        }

        /// <summary>
        /// Gets or sets a value indicating whether to Log the query to the task logs
        /// </summary>
        public bool LogQuery
        {
            get;
            set;
        }

        /// <summary>
        /// Gets DefaultServer.
        /// </summary>
        public abstract string ConnectionString
        {
            get;
        }

        /// <summary>
        /// Adds a list of WHERE clause statements to a SQL SELECT query
        /// </summary>
        /// <param name="query">The SQL SELECT statement</param>
        /// <param name="where">The list of WHERE statements</param>
        /// <returns>A SQL statement</returns>
        public string ConcatQuery(string query, List<string> where)
        {
            return ConcatQuery(query, where, null);
        }

        /// <summary>
        /// Adds a list of WHERE clause statements to a SQL SELECT query
        /// </summary>
        /// <param name="query">The SQL SELECT statement</param>
        /// <param name="where">The list of WHERE statements</param>
        /// <param name="random">if set to <c>true</c> the query results will have ORDER BY NEWID()</param>
        /// <returns>A SQL statement</returns>
        public string ConcatQuery(string query, List<string> where, bool random)
        {
            return ConcatQuery(query, where, random ? "NewId()" : null);
        }

        /// <summary>
        /// Adds a list of WHERE clause statements to a SQL SELECT query
        /// </summary>
        /// <param name="query">The SQL SELECT statement</param>
        /// <param name="where">The list of WHERE statements</param>
        /// <param name="orderBy">The orderBy clause (NULL if not needed)</param>
        /// <returns>A SQL statement</returns>
        public string ConcatQuery(string query, List<string> where, string orderBy)
        {
            query += " where (1=1)";
            query = where.Aggregate(query, (current, w) => current + (" AND (" + w + ")"));

            if (!string.IsNullOrEmpty(orderBy))
                query += " Order By " + orderBy;

            return query;

        }

        /// <summary>
        /// Performs an SQL ExecuteNonQuery against a specific database server using a trusted connection
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <returns>Number of rows effected</returns>
        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, 120);
        }

        /// <summary>
        /// Performs an SQL ExecuteNonQuery against a specific database server using a trusted connection
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <param name="timeoutInSeconds">The timeout for the query, in seconds</param>
        /// <returns>Number of rows effected</returns>
        public int ExecuteNonQuery(string query, int timeoutInSeconds)
        {
            var task = LogQuery ? Shell.ActiveTask.BeginTask("ExecuteNonQuery") : null;

            try
            {
                var conn = GetSqlConnection();
                if (task != null)
                {
                    task.AddAttribute("Query", query);
                    task.AddAttribute("Conn", conn.ConnectionString);
                }
                var cmd = new SqlCommand(query, conn);

                if (cmd.CommandTimeout < timeoutInSeconds)
                    cmd.CommandTimeout = timeoutInSeconds; // wait at least timeoutInSeconds seconds

                var result = cmd.ExecuteNonQuery();

                if (task != null)
                    task.Finish();
                return result;
            }
            catch (Exception e)
            {
                if (task != null)
                    task.Finish(e);
                throw;
            }
        }

        /// <summary>
        /// Performs a SQL query against a specific database server using a trusted connection
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <param name="conn">The db connection to use</param>
        /// <returns>A SqlDataReader containing the results of the query</returns>
        public SqlDataReader PerformQuery(string query, SqlConnection conn)
        {
            var task = LogQuery ? Shell.ActiveTask.BeginTask("PerformQuery") : null;
            if (task != null)
            {
                task.AddAttribute("Connection", conn.ConnectionString);
                task.AddAttribute("Query", query);
            }

            try
            {
                var errorcount = 0;

                do
                {
                    try
                    {
                        var cmd = new SqlCommand(query, conn);

                        if (cmd.CommandTimeout < 120)
                            cmd.CommandTimeout = 120; // wait 2 minutes

                        var result = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                        if (task != null)
                        {
                            task.AddAttribute("HasRows", result.HasRows.ToString());
                            task.Finish();
                            task = null;
                        }

                        return result;
                    }
                    catch (Exception e)
                    {
                        if (e.Message.Contains("Invalid column name"))
                        {
                            throw;
                        }

                        if (e is SqlException)
                        {
                            if (task != null)
                                task.AddLog(LogStatus.Warning, "Exception absorbed during query: " + e.Message);
                            Shell.TestSession.Browser.Sleep(2000); //wait 2 seconds
                            var throwE = false;
                            try
                            {
                                if (conn.State == ConnectionState.Open)
                                    conn.Close();
                                SqlConnection.ClearPool(conn);
                                conn.Open();
                            }
                            catch (Exception e2)
                            {
                                // can't restart the connection, so bail
                                if (task != null)
                                {
                                    task.AddLog(LogStatus.Fail, "Failed to restart connection: " + e2.Message);
                                }

                                throwE = true;
                            }

                            if (throwE)
                                throw;

                            errorcount++;
                        }
                        else
                        {
                            throw;
                        }
                    }
                } while (errorcount < 3);

                throw new SimpleDataAdapterException(this, LogStatus.FailTimeout,
                                                     "Giving up, ran out of retries for query");
            }
            catch (Exception ex)
            {
                if (task != null)
                    task.Finish(ex);
                throw;
            }
        }

        /// <summary>
        /// Performs a simple SQL query and returns the result(s)
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public SqlDataReader PerformQuery(string query)
        {
            return PerformQuery(query, GetSqlConnection());
        }

        /// <summary>
        /// <see cref="PerformQueryAsDataTable(string,System.Data.SqlClient.SqlConnection)" />
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <returns>A SqlDataReader containing the results of the query</returns>
        public DataTable PerformQueryAsDataTable(string query)
        {
            return PerformQueryAsDataTable(query, GetSqlConnection());
        }
        
        /// <summary>
        /// Performs a SQL query against a specific database server using a trusted connection
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <param name="conn">The connection to use</param>
        /// <returns>A SqlDataReader containing the results of the query</returns>
        public DataTable PerformQueryAsDataTable(string query, SqlConnection conn)
        {
            var task = LogQuery ? Shell.ActiveTask.BeginTask("PerformQueryAsDataTable") : null;
            if (task != null)
            {
                task.AddAttribute("Connection", conn.ConnectionString);
                task.AddAttribute("Query", query);
            }

            try
            {
                using (var da = new SqlDataAdapter(query, conn))
                {
                    var ds = new DataSet();
                    if (da.SelectCommand.CommandTimeout < 120)
                        da.SelectCommand.CommandTimeout = 120; // wait 2 minutes
                    da.Fill(ds);
                    var dt = ds.Tables[0];

                    if (task != null)
                        task.Finish();

                    return dt;
                }
            }
            catch (Exception e)
            {
                if (task != null)
                    task.Finish(e);

                throw;
            }
        }

        /// <summary>
        /// Performs a SQL query against a specific database server using a trusted connection
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <returns>A DataRow (or null) containing the results of the query</returns>
        public DataRow PerformQueryAsDataRow(string query)
        {
            var task = LogQuery ? Shell.ActiveTask.BeginTask("PerformQueryAsDataRow") : null;
            try
            {
                var dt = PerformQueryAsDataTable(query);
                var dr = dt.Rows.Count > 0 ? dt.Rows[RandomStuff.RandomNumber(0, dt.Rows.Count - 1)] : null;

                if (task != null)
                    task.Finish();

                return dr;
            }
            catch (Exception e)
            {
                if (task != null)
                    task.Finish(e);

                throw;
            }
        }

        /// <summary>
        /// Performs the query as data row.
        /// </summary>
        /// <param name="query">The SQL query.</param>
        /// <param name="where">The list of where statements to append to the query.</param>
        /// <returns></returns>
        public DataRow PerformQueryAsDataRow(string query, List<string> where)
        {
            return PerformQueryAsDataRow(ConcatQuery(query, where));
        }

        /// <summary>
        /// Performs a SQL query against a specific database server using a trusted connection
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <param name="where">A collection of where clauses to append to the query</param>
        /// <returns>A SqlDataReader containing the results of the query</returns>
        public SqlDataReader PerformQuery(string query, List<string> where)
        {
            return PerformQuery(query, where, false);
        }

        /// <summary>
        /// Performs a SQL query against a specific database server using a trusted connection
        /// </summary>
        /// <param name="query">The query to execute</param>
        /// <param name="where">A collection of where clauses to append to the query</param>
        /// <param name="random">Whether to add random order by</param>
        /// <returns>A SqlDataReader containing the results of the query</returns>
        public SqlDataReader PerformQuery(string query, List<string> where, bool random)
        {
            return PerformQuery(ConcatQuery(query, where, random));
        }

        /// <summary>
        /// Returns a default sql connection
        /// </summary>
        /// <returns>a new SqlConnection</returns>
        public virtual SqlConnection GetSqlConnection()
        {
            return GetSqlConnection(ConnectionString);
        }

        private static int _getSqlCounter;
        /// <summary>
        /// Returns a sql connection using a specific connection string
        /// </summary>
        /// <param name="connectionstring">the connection string</param>
        /// <returns>a sql connection</returns>
        public SqlConnection GetSqlConnection(string connectionstring)
        {
            var mine = System.Threading.Interlocked.Increment(ref _getSqlCounter);
            var task = Shell.ActiveTask.BeginTask("GetSqlConnection (" + mine + ")");
            task.AddAttribute("constr", connectionstring);
            try
            {
                for (var retry = 0;; retry++)
                {
                    try
                    {
                        var conn = new SqlConnection(connectionstring);
                        conn.Open();
                        task.Finish();
                        return conn;
                    }
                    catch (Exception ex)
                    {
                        task.AddLog(LogStatus.Information, "Try #" + retry + " fail: " + ex.Message);
                        if (retry > 3)
                            throw;
                        if (!ex.Message.Contains("obtaining a connection from the pool") && !ex.Message.Contains("timeout"))
                            throw;

                        System.Threading.Thread.Sleep(1000);
                    }
                }
            }
            catch (Exception e)
            {
                task.Finish(e);
                throw;
            }
        }

        /// <summary>
        /// An exception class for the SAJDataAdapter
        /// </summary>
        public class SimpleDataAdapterException : LogStatusException
        {
            /// <summary>
            /// Initializes a new instance of the <see cref="SimpleDataAdapterException"/> class.
            /// </summary>
            /// <param name="data">The data adapter.</param>
            /// <param name="message">The message.</param>
            public SimpleDataAdapterException(SimpleDataAdapter<TShell> data, string message)
                : this(data, LogStatus.Fail, message)
            {
            }

            /// <summary>
            /// Initializes a new instance of the <see cref="SimpleDataAdapterException"/> class.
            /// </summary>
            /// <param name="data">The data adapter.</param>
            /// <param name="status">The status to log</param>
            /// <param name="message">The message.</param>
            public SimpleDataAdapterException(SimpleDataAdapter<TShell> data, LogStatus status, string message)
                : base(status, message)
            {
                DataAdapter = data;
            }

            /// <summary>
            /// Gets DataAdapter.
            /// </summary>
            public SimpleDataAdapter<TShell> DataAdapter
            {
                get;
                private set;
            }
        }
    }
}
